package pe.gob.osinergmin.interfase_siaf.dac.ingreso.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import pe.gob.osinergmin.interfase_siaf.dac.common.dao.impl.Conexion;
import pe.gob.osinergmin.interfase_siaf.dac.domain.interfase.IntfCab;
import pe.gob.osinergmin.interfase_siaf.dac.domain.interfase.IntfDet09;
import pe.gob.osinergmin.interfase_siaf.dac.domain.interfase.IntfNot;
import pe.gob.osinergmin.interfase_siaf.dac.ingreso.dao.IngresoDao;
import pe.gob.osinergmin.interfase_siaf.dac.util.Constantes;

@Repository("ingresoDao")
public class IngresoDaoImpl implements IngresoDao {
	
	
	private final Log logger = LogFactory.getLog(getClass());
 	
	
	  @Value("${jdbc.driverClassName}")
	  private String driverClassName;

	  @Value("${jdbc.urlInterfase}")
	  private String url;

	  @Value("${jdbc.username}")
	  private String userName;

	  @Value("${jdbc.password}")
	  private String password;	 
	  
	  @Value("${jdbc.driverClassNameODBC}")
	  private String driverClassNameODBC;
	  
	  @Value("${jdbc.urlInterfaseODBC}")
	  private String urlODBC;
	  

	
	/**DECLARACION DE SQL**/
	
	private static final String SQL_INSERT_DET09 ="INSERT INTO INTF_DET09 (SECUENCIAL,ID_CLASIFI,CLASIFNIV1,CLASIFNIV2,"
			+ " CLASIFNIV3,CLASIFNIV4,CLASIFNIV5,CLASIFNIV6,SEC_FUNC,CLASIF2N1,CLASIF2N2,"
			+ " CLASIF2N3,CLASIF2N4,CLASIF2N5,CLASIF2N6,MONTO,MONTO_MN,ID_CLASIF2) "
			+ " VALUES (?, ?, ?, ?, ?, ?, ?,"
			+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";	
	
	private static final String SQL_INSERT_NOT ="INSERT INTO INTF_NOT (SECUENCIAL,CORRELATIV,NOTA) "
			+ " VALUES (?, ?, ?)";	
	
	/***GRABANDO DATOS PARA INGRESOS ETAPA DETERMINADO****/
	
	private static final String SQL_INSERT_CAB_DETERMINADO ="INSERT INTO INTF_CAB (SECUENCIAL,ANO_EJE,SEC_EJEC,CICLO,FASE,"
			+ " SEC_EJEC2,MES_EJE,TIPO_OPE,MOD_COMP,SEC_AREA,COD_DOC,SERIE_DOC,"
			+ " NUM_DOC,FEC_DOC,M_FECHA,TIPO_ID,RUC,ORIGEN,FTE_FIN,T_PAGO,T_RECURS, T_COMPRO,MONEDA,T_CAMBIO,"
			+ " UE_ENVIO,UE_ESTADO,INTF_EXP,ESTADO_REG,DATO_ORI,TIPO_FIN,ESTADO,INTF_FASE,INTF_SEC,INTF_COR) "
			+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
			+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";  
   
	@Override
	public int insertarIngresosDeterminado(Map<String, Object> params) throws Exception{
		 Connection cn = null;		
		 PreparedStatement insertCab = null;
		 PreparedStatement insertDet09 = null;
		 PreparedStatement insertNot = null;	
		 int valor =0;
		 int valor1=0;
		 int valor2 = 0;
		 IntfCab c = (IntfCab) params.get("intfCab");   
	     @SuppressWarnings("unchecked")
		 List<IntfDet09> detLista = (List<IntfDet09>) params.get("listaDet");			 
		 IntfNot n = (IntfNot) params.get("intfNot");	
		 try {
			 
			 /***conexion con la libreria stateles desde el archivo properties*/
		     cn = Conexion.getConexion(driverClassName,url,userName,password);	   
		     
		     cn.setAutoCommit(false);
		    
		     /**INSERTAR EN CAB**/			
		      // create PreparedStatement object for INSERT operation		   
		     insertCab = cn.prepareStatement(SQL_INSERT_CAB_DETERMINADO);
		     insertDet09 = cn.prepareStatement(SQL_INSERT_DET09);
		     insertNot =   cn.prepareStatement(SQL_INSERT_NOT);
		     
		     
		     insertCab.setString(1, c.getSecuencial());
		     insertCab.setString(2, c.getAnoEje());
		     insertCab.setString(3, c.getSecEjec());
		     insertCab.setString(4, c.getCiclo());
		     insertCab.setString(5, c.getFase()); 
		     insertCab.setString(6, c.getSecEjec2());
		     insertCab.setString(7, c.getMesEje());	
		     insertCab.setString(8, c.getTipoOpe());
		     insertCab.setString(9, c.getModComp());	    
		     insertCab.setString(10, c.getSecArea());
		     insertCab.setString(11, c.getCodDoc());
		     insertCab.setString(12, c.getSerieDoc());
		     insertCab.setString(13, c.getNumDoc());
		     if(c.getFecDoc()!=null){
		    	 insertCab.setDate(14, new Date(c.getFecDoc().getTime())); 
		     }else{
		    	 insertCab.setNull(14, java.sql.Types.DATE);
		     }
		     if(c.getmFecha()!=null){
		    	 insertCab.setDate(15, new Date(c.getmFecha().getTime()));  
		     }else{
		    	 insertCab.setNull(15, java.sql.Types.DATE);
		     }		    
		     insertCab.setString(16, c.getTipoId());
		     insertCab.setString(17, c.getRuc());
		     insertCab.setString(18, c.getOrigen());
		     insertCab.setString(19, c.getFteFin());	  
		     insertCab.setString(20, c.gettPago());
		     insertCab.setString(21, c.gettRecurs());
		     insertCab.setString(22, c.gettCompro());	    
		     insertCab.setString(23, c.getMoneda().toUpperCase());
		     if(c.gettCambio()!=null){
		    	 insertCab.setDouble(24, c.gettCambio()); 
		     }else{
		    	 insertCab.setNull(24, java.sql.Types.DOUBLE);  
		     }		         
		     insertCab.setString(25, c.getUeEnvio());     
		     insertCab.setString(26, c.getUeEstado());    
		     insertCab.setString(27, c.getIntfExp());	
		     insertCab.setString(28, c.getEstadoReg());
		     insertCab.setString(29, c.getDatoOri());
		     insertCab.setString(30, c.getTipoFin());
		     insertCab.setString(31, c.getEstado());
		     insertCab.setString(32, c.getIntfFase());
		     insertCab.setString(33, c.getIntfSec());	
		     insertCab.setString(34, c.getIntfCor());
		  
		     valor = insertCab.executeUpdate();	
		     
		     	     
		     logger.info("VALOR DEL GRABADO CAB:  "+valor);	     
		     
		     /**INSERTAR EN DET09**/ 
		     for(IntfDet09 d:detLista){
		    	 insertDet09.setString(1, d.getSecuencial());
			     insertDet09.setString(2, d.getIdClasifi());
			     insertDet09.setString(3, d.getClasifniv1());
			     insertDet09.setString(4, d.getClasifniv2());
			     insertDet09.setString(5, d.getClasifniv3());
			     insertDet09.setString(6, d.getClasifniv4());
			     insertDet09.setString(7, d.getClasifniv5());
			     insertDet09.setString(8, d.getClasifniv6());
			     insertDet09.setString(9, d.getSecFunc());
			     insertDet09.setString(10, d.getClasif2n1());
			     insertDet09.setString(11, d.getClasif2n2());
			     insertDet09.setString(12, d.getClasif2n3());
			     insertDet09.setString(13, d.getClasif2n4());
			     insertDet09.setString(14, d.getClasif2n5());
			     insertDet09.setString(15, d.getClasif2n6());
			     if(d.getMonto()!=null){
			    	 insertDet09.setDouble(16, d.getMonto()); 
			     }else{
			    	 insertDet09.setNull(16, java.sql.Types.DOUBLE);  
			     }	
			     if(d.getMontoMn()!=null){
			    	  insertDet09.setDouble(17, d.getMontoMn());
			     }else{
			    	 insertDet09.setNull(17, java.sql.Types.DOUBLE);	  
			     }		    
			     insertDet09.setString(18, d.getIdClasif2());			 		    
			     valor1 = insertDet09.executeUpdate();			   	     
			     logger.info("VALOR DEL GRABADO DET09:  "+valor1); 
		       }		    	
		     
		     /**INSERTAR EN NOT**/ 			   
		    if(Constantes.DETERMINADO_NUEVO.equals(c.getEstado())){
		    	 insertNot.setString(1, n.getSecuencial());
			     insertNot.setString(2, n.getCorrelativ());
			     insertNot.setString(3, n.getNota());			 
		    	 valor2 = insertNot.executeUpdate();
		    	 logger.info("VALOR DEL GRABADO NOT:  "+valor2);	
		    }		    
		    /****Comiteaando**/		    
		    cn.rollback();	 
		   return 1;
		} catch (Exception e) {			
			  logger.info("Error al grabar datos ingreso rollbac" +e); 			
			if (cn != null) {
	            try { 
	            	logger.info("NO HIZO COMMIT");
	                logger.info("Error al grabar datos ingreso rollbac1"); 
	                cn.rollback();	        
	                cn.close();	         
	            } catch(Exception excep) {
	            	  logger.info("Error al grabar datos ingreso rollbac" +e); 
	            }
	        }			
			return 0;
		}finally{			
			logger.info("Cerrando el preparedstamenrt del finally"); 
			 if (insertCab != null) {
				 insertCab.close();
			 }
			 if (insertDet09 != null) {
				 insertDet09.close();
			 }
			 if (insertNot != null) {
				 insertNot.close();
			 }
	         cn.setAutoCommit(true);
			 cn.close();
			 if(Constantes.DETERMINADO_NUEVO.equals(c.getEstado())){
				if(valor==1 && valor1==1 && valor2==1){
					  insertarIngresosDeterminado_1(c,detLista,n);
				}
			   }else{
				  if(valor==1 && valor1==1){
					   insertarIngresosDeterminado_1(c,detLista,n);	 
				 }		   
			 }				 
		}
		
	}
	
	private void insertarIngresosDeterminado_1(IntfCab c,List<IntfDet09> lista,IntfNot n) throws Exception{
		 Connection cn = null;		
		 PreparedStatement insertCab = null;
		 PreparedStatement insertDet09 = null;
		 PreparedStatement insertNot = null;	
		 int valor =0;
		 int valor1=0;
		 int valor2 = 0;
		 try {	     
		     /***conexion con odbc  desde el archivo properties*/
			 cn = Conexion.getConexionODBC(driverClassNameODBC,urlODBC,userName,password);	
		     
		     cn.setAutoCommit(false);
		     
		     /**INSERTAR EN CAB**/			
		      // create PreparedStatement object for INSERT operation		   
		     insertCab = cn.prepareStatement(SQL_INSERT_CAB_DETERMINADO);
		     insertDet09 = cn.prepareStatement(SQL_INSERT_DET09);
		     insertNot =   cn.prepareStatement(SQL_INSERT_NOT);
		     
		     insertCab.setString(1, c.getSecuencial());
		     insertCab.setString(2, c.getAnoEje());
		     insertCab.setString(3, c.getSecEjec());
		     insertCab.setString(4, c.getCiclo());
		     insertCab.setString(5, c.getFase());     
		     insertCab.setString(6, c.getSecEjec2());	
		     insertCab.setString(7, c.getMesEje());
		     insertCab.setString(8, c.getTipoOpe());
		     insertCab.setString(9, c.getModComp());	    
		     insertCab.setString(10, c.getSecArea());
		     insertCab.setString(11, c.getCodDoc());
		     insertCab.setString(12, c.getSerieDoc());
		     insertCab.setString(13, c.getNumDoc());
		     if(c.getFecDoc()!=null){
		    	 insertCab.setDate(14, new Date(c.getFecDoc().getTime())); 
		     }else{
		    	 insertCab.setNull(14, java.sql.Types.DATE);
		     }
		     if(c.getmFecha()!=null){
		    	 insertCab.setDate(15, new Date(c.getmFecha().getTime()));  
		     }else{
		    	 insertCab.setNull(15, java.sql.Types.DATE);	 
		     }		    
		     insertCab.setString(16, c.getTipoId());
		     insertCab.setString(17, c.getRuc());
		     insertCab.setString(18, c.getOrigen());
		     insertCab.setString(19, c.getFteFin());  
		     insertCab.setString(20, c.gettPago());
		     insertCab.setString(21, c.gettRecurs());
		     insertCab.setString(22, c.gettCompro());	    
		     insertCab.setString(23, c.getMoneda().toUpperCase());
		     if(c.gettCambio()!=null){
		    	 insertCab.setDouble(24, c.gettCambio());		 
		     }else{
		    	 insertCab.setNull(24, java.sql.Types.DOUBLE);	  
		     }		         
		     insertCab.setString(25, c.getUeEnvio());		     
		     insertCab.setString(26, c.getUeEstado());	    
		     insertCab.setString(27, c.getIntfExp());	
		     insertCab.setString(28, c.getEstadoReg());
		     insertCab.setString(29, c.getDatoOri());
		     insertCab.setString(30, c.getTipoFin());
		     insertCab.setString(31, c.getEstado());	
		     insertCab.setString(32, c.getIntfFase());	
		     insertCab.setString(33, c.getIntfSec());	
		     insertCab.setString(34, c.getIntfCor());	
		  
		     valor = insertCab.executeUpdate();	
		     
		     	     
		     logger.info("VALOR DEL GRABADO CAB:  "+valor);		     
		     
		     /**INSERTAR EN DET09**/ 
		     for(IntfDet09 d:lista){
		    	 insertDet09.setString(1, d.getSecuencial());
			     insertDet09.setString(2, d.getIdClasifi());
			     insertDet09.setString(3, d.getClasifniv1());
			     insertDet09.setString(4, d.getClasifniv2());
			     insertDet09.setString(5, d.getClasifniv3());
			     insertDet09.setString(6, d.getClasifniv4());
			     insertDet09.setString(7, d.getClasifniv5());
			     insertDet09.setString(8, d.getClasifniv6());
			     insertDet09.setString(9, d.getSecFunc());
			     insertDet09.setString(10, d.getClasif2n1());
			     insertDet09.setString(11, d.getClasif2n2());
			     insertDet09.setString(12, d.getClasif2n3());
			     insertDet09.setString(13, d.getClasif2n4());
			     insertDet09.setString(14, d.getClasif2n5());
			     insertDet09.setString(15, d.getClasif2n6());
			     if(d.getMonto()!=null){
			    	 insertDet09.setDouble(16, d.getMonto()); 
			     }else{
			    	 insertDet09.setNull(16, java.sql.Types.DOUBLE);	  
			     }	
			     if(d.getMontoMn()!=null){
			    	  insertDet09.setDouble(17, d.getMontoMn());
			     }else{
			    	 insertDet09.setNull(17, java.sql.Types.DOUBLE);	  
			     }		    
			     insertDet09.setString(18, d.getIdClasif2());			  
			    
			     valor1 = insertDet09.executeUpdate();	   	     
			     logger.info("VALOR DEL GRABADO DET09:  "+valor1); 
		     }    	
		     
		     /**INSERTAR EN NOT**/ 			   
		    if(Constantes.DETERMINADO_NUEVO.equals(c.getEstado())){
		    	 insertNot.setString(1, n.getSecuencial());
			     insertNot.setString(2, n.getCorrelativ());
			     insertNot.setString(3, n.getNota());			  
		    	 valor2 = insertNot.executeUpdate();
		    	 logger.info("VALOR DEL GRABADO NOT:  "+valor2);	
		    }		    
		    /****todo esta ok commit**/			   
		    cn.commit();		
		} catch (Exception e) {			
			  logger.info("Error al grabar datos ingreso rollbac" +e); 			
			if (cn != null) {
	            try { 
	            	logger.info("NO HIZO COMMIT");
	                logger.info("Error al grabar datos ingreso rollbac1"); 
	                cn.rollback();	              
	                cn.close();	             
	            } catch(Exception excep) {
	            	  logger.info("Error al grabar datos ingreso rollbac" +e); 
	            }
	        }	
		}finally{			
			logger.info("Cerrando el preparedstamenrt del finally"); 
			 if (insertCab != null) {
				 insertCab.close();
			 }
			 if (insertDet09 != null) {
				 insertDet09.close();
			 }
			 if (insertNot != null) {
				 insertNot.close();
			 }
	         cn.setAutoCommit(true);
			 cn.close();			 
		}		
	}	
	/***GRABANDO DATOS PARA INGRESOS ETAPA RECAUDADO****/
	private static final String SQL_INSERT_CAB_RECAUDADO ="INSERT INTO INTF_CAB ("
			+ " SECUENCIAL,ANO_EJE,SEC_EJEC,CICLO,FASE,COD_DOC,SERIE_DOC,NUM_DOC,FEC_DOC,M_FECHA,"
			+ " ORIGEN,FTE_FIN,T_PAGO,T_RECURS,T_COMPRO,ANO_CTA,BANCO,CTA_CTE,MONEDA,T_CAMBIO,"
			+ " ANO_CTA2,BANCO2,CTA_CTE2,DCOD_DOC,DNUM_DOC,DFEC_DOC,DNOMBRE,DMONTO,DMONTO_MN,UE_ENVIO,"
			+ " UE_ESTADO,INTF_EXP,INTF_FASE,INTF_SEC,INTF_COR,ESTADO_REG,DATO_ORI,ESTADO,MES_EJE) "
			+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
			+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
	@Override
	public int insertarIngresosRecaudado(Map<String, Object> params) throws Exception{
		 Connection cn = null;
		 PreparedStatement insertCab = null;
		 PreparedStatement insertDet09 = null;
		 PreparedStatement insertNot = null;
		 int valor =0;
		 int valor1= 0;
		 int valor2 = 0;
		 IntfCab c = (IntfCab) params.get("intfCab");   
	     @SuppressWarnings("unchecked")
		 List<IntfDet09> detLista = (List<IntfDet09>) params.get("listaDet");			 
		 IntfNot n = (IntfNot) params.get("intfNot");	
		 try {
			 
			 /***conexion con la libreria stateles desde el archivo properties*/
		     cn = Conexion.getConexion(driverClassName,url,userName,password);	  
		       
		     cn.setAutoCommit(false);
		     
		     insertCab = cn.prepareStatement(SQL_INSERT_CAB_RECAUDADO);
		     insertDet09 = cn.prepareStatement(SQL_INSERT_DET09);
		     insertNot =   cn.prepareStatement(SQL_INSERT_NOT);
		     
		     /**INSERTAR EN CAB**/		    
		     insertCab.setString(1, c.getSecuencial());
		     insertCab.setString(2, c.getAnoEje());
		     insertCab.setString(3, c.getSecEjec());		     
		     insertCab.setString(4, c.getCiclo());
		     insertCab.setString(5, c.getFase());	     
		     insertCab.setString(6, c.getCodDoc());
		     insertCab.setString(7, c.getSerieDoc());
		     insertCab.setString(8, c.getNumDoc());
		     if(c.getFecDoc()!=null){
		    	 insertCab.setDate(9, new Date(c.getFecDoc().getTime())); 
		     }else{
		    	 insertCab.setNull(9, java.sql.Types.DATE); 
		     }
		     if(c.getmFecha()!=null){
		    	 insertCab.setDate(10, new Date(c.getmFecha().getTime()));	  
		     }else{
		    	 insertCab.setNull(10, java.sql.Types.DATE);	 
		     }		    	     
		     insertCab.setString(11, c.getOrigen());
		     insertCab.setString(12, c.getFteFin());	     
		     insertCab.setString(13, c.gettPago());
		     insertCab.setString(14, c.gettRecurs());
		     insertCab.setString(15, c.gettCompro());		     
		     insertCab.setString(16, c.getAnoCta());
		     insertCab.setString(17, c.getBanco());
		     insertCab.setString(18, c.getCtaCte());		     
		     insertCab.setString(19, c.getMoneda().toUpperCase());
		     if(c.gettCambio()!=null){
		    	 insertCab.setDouble(20, c.gettCambio());		 
		     }else{
		    	 insertCab.setNull(20, java.sql.Types.DOUBLE);	  
		     }		    
		     insertCab.setString(21, c.getAnoCta2());
		     insertCab.setString(22, c.getBanco2());
		     insertCab.setString(23, c.getCtaCte2());	
		     insertCab.setString(24, c.getDcodDoc());
		     insertCab.setString(25, c.getDnumDoc());
		 
		     if(c.getDfecDoc()!=null){
		    	 insertCab.setDate(26, new Date(c.getDfecDoc().getTime()));		  
		     }else{
		    	 insertCab.setNull(26, java.sql.Types.DATE);	 	 
		     }	
		     
		     insertCab.setString(27, c.getDnombre());
		     
		     if(c.getDmonto()!=null){
		    	 insertCab.setDouble(28, c.getDmonto()); 
		     }else{
		    	 insertCab.setNull(28, java.sql.Types.DOUBLE);	  
		     }	
		     if(c.getDmontoMn()!=null){
		    	 insertCab.setDouble(29, c.getDmontoMn());	 
		     }else{
		    	 insertCab.setNull(29, java.sql.Types.DOUBLE);  
		     }			          
		     insertCab.setString(30, c.getUeEnvio());
		     insertCab.setString(31, c.getUeEstado());
		     insertCab.setString(32, c.getIntfExp());		     
		     insertCab.setString(33, c.getIntfFase());
		     insertCab.setString(34, c.getIntfSec());
		     insertCab.setString(35, c.getIntfCor());		     
		     insertCab.setString(36, c.getEstadoReg());
		     insertCab.setString(37, c.getDatoOri());	
		     insertCab.setString(38, c.getEstado());	
		     insertCab.setString(39, c.getMesEje());
		     
		     valor= insertCab.executeUpdate();
		     
		     logger.info("VALOR DEL GRABADO CAT:  "+valor);
		     
		     /**INSERTAR EN DET09**/    
		     for(IntfDet09 d:detLista){
		    	 insertDet09.setString(1, d.getSecuencial());
			     insertDet09.setString(2, d.getIdClasifi());
			     insertDet09.setString(3, d.getClasifniv1());
			     insertDet09.setString(4, d.getClasifniv2());
			     insertDet09.setString(5, d.getClasifniv3());
			     insertDet09.setString(6, d.getClasifniv4());
			     insertDet09.setString(7, d.getClasifniv5());
			     insertDet09.setString(8, d.getClasifniv6());
			     insertDet09.setString(9, d.getSecFunc());
			     insertDet09.setString(10, d.getClasif2n1());
			     insertDet09.setString(11, d.getClasif2n2());
			     insertDet09.setString(12, d.getClasif2n3());
			     insertDet09.setString(13, d.getClasif2n4());
			     insertDet09.setString(14, d.getClasif2n5());
			     insertDet09.setString(15, d.getClasif2n6());
			     if(d.getMonto()!=null){
			    	 insertDet09.setDouble(16, d.getMonto()); 
			     }else{
			    	 insertDet09.setNull(16, java.sql.Types.DOUBLE);
			     }	
			     if(d.getMontoMn()!=null){
			    	  insertDet09.setDouble(17, d.getMontoMn());
			     }else{
			    	 insertDet09.setNull(17, java.sql.Types.DOUBLE);  
			     }			     
			     insertDet09.setString(18, d.getIdClasif2());					     
			     valor1 = insertDet09.executeUpdate();		     
			     logger.info("VALOR DEL GRABADO DET:  "+valor1); 
		     }
		     
		     /**INSERTAR EN NOT**/		    
		     if(Constantes.RECAUDADO_NUEVO.equals(c.getEstado())){
		    	 insertNot.setString(1, n.getSecuencial());
			     insertNot.setString(2, n.getCorrelativ());
			     insertNot.setString(3, n.getNota());			  
		    	 valor2 = insertNot.executeUpdate();
		    	 logger.info("VALOR DEL GRABADO NOT:  "+valor2);	
		    }	     
		    cn.rollback();
		    return 1;
		} catch (Exception e) {
			logger.info("Error al grabar datos ingreso rollbac recaudado" +e); 	   
			if (cn != null) {
	            try {
	                logger.info("Error al grabar datos ingreso rollbac recaudado"); 	               
	                logger.info("NO HIZO COMMIT");
	                logger.info("Error al grabar datos ingreso rollbac"); 
	               cn.rollback();
	               cn.close();
	            } catch(Exception excep) {
	            	logger.info("Error al grabar datos ingreso rollbac" +e); 
	            }
	        }
			return 0;
		}finally{
			 if (insertCab != null) {
				 insertCab.close();
			 }
			 if (insertDet09 != null) {
				 insertDet09.close();
			 }
			 if (insertNot != null) {
				 insertNot.close();
			 }
			cn.setAutoCommit(true);			
			cn.close();
			if(Constantes.RECAUDADO_NUEVO.equals(c.getEstado())){
				if(valor==1 && valor1==1 && valor2==1){
					insertarIngresosRecaudado_1(c,detLista,n);
				}
			}else{
				if(valor==1 && valor1==1){
					insertarIngresosRecaudado_1(c,detLista,n);	 
				}		   
			}
		}		
	}
		
	private void insertarIngresosRecaudado_1(IntfCab c,List<IntfDet09> lista,IntfNot n) throws Exception{
		 Connection cn = null;
		 PreparedStatement insertCab = null;
		 PreparedStatement insertDet09 = null;
		 PreparedStatement insertNot = null;
		 try {		
		     /***conexion con ODBC*/
		     cn = Conexion.getConexionODBC(driverClassNameODBC,urlODBC,userName,password);  
		       
		     cn.setAutoCommit(false);
		     
		     insertCab = cn.prepareStatement(SQL_INSERT_CAB_RECAUDADO);
		     insertDet09 = cn.prepareStatement(SQL_INSERT_DET09);
		     insertNot =   cn.prepareStatement(SQL_INSERT_NOT);
		     
		     /**INSERTAR EN CAB**/		    
		     insertCab.setString(1, c.getSecuencial());
		     insertCab.setString(2, c.getAnoEje());
		     insertCab.setString(3, c.getSecEjec());		     
		     insertCab.setString(4, c.getCiclo());
		     insertCab.setString(5, c.getFase());	     
		     insertCab.setString(6, c.getCodDoc());
		     insertCab.setString(7, c.getSerieDoc());
		     insertCab.setString(8, c.getNumDoc());
		     if(c.getFecDoc()!=null){
		    	 insertCab.setDate(9, new Date(c.getFecDoc().getTime())); 	 
		     }else{
		    	 insertCab.setNull(9, java.sql.Types.DATE);	 
		     }
		     if(c.getmFecha()!=null){
		    	 insertCab.setDate(10, new Date(c.getmFecha().getTime()));	  
		     }else{
		    	 insertCab.setNull(10, java.sql.Types.DATE); 	 
		     }		    	     
		     insertCab.setString(11, c.getOrigen());
		     insertCab.setString(12, c.getFteFin());	     
		     insertCab.setString(13, c.gettPago());
		     insertCab.setString(14, c.gettRecurs());
		     insertCab.setString(15, c.gettCompro());		     
		     insertCab.setString(16, c.getAnoCta());
		     insertCab.setString(17, c.getBanco());
		     insertCab.setString(18, c.getCtaCte());		     
		     insertCab.setString(19, c.getMoneda().toUpperCase());
		     if(c.gettCambio()!=null){
		    	 insertCab.setDouble(20, c.gettCambio());		 
		     }else{
		    	 insertCab.setNull(20, java.sql.Types.DOUBLE);	  
		     }		    
		     insertCab.setString(21, c.getAnoCta2());
		     insertCab.setString(22, c.getBanco2());
		     insertCab.setString(23, c.getCtaCte2());	
		     insertCab.setString(24, c.getDcodDoc());
		     insertCab.setString(25, c.getDnumDoc());		
		     if(c.getDfecDoc()!=null){
		    	 insertCab.setDate(26, new Date(c.getDfecDoc().getTime()));		  
		     }else{
		    	 insertCab.setNull(26, java.sql.Types.DATE);	 
		     }	
		     
		     insertCab.setString(27, c.getDnombre());
		     
		     if(c.getDmonto()!=null){
		    	 insertCab.setDouble(28, c.getDmonto());	 
		     }else{
		    	 insertCab.setNull(28, java.sql.Types.DOUBLE);	  
		     }	
		     if(c.getDmontoMn()!=null){
		    	 insertCab.setDouble(29, c.getDmontoMn());	 
		     }else{
		    	 insertCab.setNull(29, java.sql.Types.DOUBLE);	  
		     }			          
		     insertCab.setString(30, c.getUeEnvio());
		     insertCab.setString(31, c.getUeEstado());
		     insertCab.setString(32, c.getIntfExp());		     
		     insertCab.setString(33, c.getIntfFase());
		     insertCab.setString(34, c.getIntfSec());
		     insertCab.setString(35, c.getIntfCor());		     
		     insertCab.setString(36, c.getEstadoReg());
		     insertCab.setString(37, c.getDatoOri());	
		     insertCab.setString(38, c.getEstado());	
		     insertCab.setString(39, c.getMesEje());
		     
		     int valor= insertCab.executeUpdate();
		     
		     logger.info("VALOR DEL GRABADO CAT:  "+valor);
		     
		     /**INSERTAR EN DET09**/    
		     for(IntfDet09 d:lista){
		    	 insertDet09.setString(1, d.getSecuencial());
			     insertDet09.setString(2, d.getIdClasifi());
			     insertDet09.setString(3, d.getClasifniv1());
			     insertDet09.setString(4, d.getClasifniv2());
			     insertDet09.setString(5, d.getClasifniv3());
			     insertDet09.setString(6, d.getClasifniv4());
			     insertDet09.setString(7, d.getClasifniv5());
			     insertDet09.setString(8, d.getClasifniv6());
			     insertDet09.setString(9, d.getSecFunc());
			     insertDet09.setString(10, d.getClasif2n1());
			     insertDet09.setString(11, d.getClasif2n2());
			     insertDet09.setString(12, d.getClasif2n3());
			     insertDet09.setString(13, d.getClasif2n4());
			     insertDet09.setString(14, d.getClasif2n5());
			     insertDet09.setString(15, d.getClasif2n6());
			     if(d.getMonto()!=null){
			    	 insertDet09.setDouble(16, d.getMonto()); 
			     }else{
			    	 insertDet09.setNull(16, java.sql.Types.DOUBLE);	  
			     }	
			     if(d.getMontoMn()!=null){
			    	  insertDet09.setDouble(17, d.getMontoMn());
			     }else{
			    	 insertDet09.setNull(17, java.sql.Types.DOUBLE);	  
			     }			     
			     insertDet09.setString(18, d.getIdClasif2());			  		     
			     int valor1 = insertDet09.executeUpdate();		     
			     logger.info("VALOR DEL GRABADO DET:  "+valor1);	 
		     }
		     
		     /**INSERTAR EN NOT**/
		     int valor2 =0;
		     if(Constantes.RECAUDADO_NUEVO.equals(c.getEstado())){
		    	 insertNot.setString(1, n.getSecuencial());
			     insertNot.setString(2, n.getCorrelativ());
			     insertNot.setString(3, n.getNota());			 
		    	 valor2 = insertNot.executeUpdate();
		    	 logger.info("VALOR DEL GRABADO NOT:  "+valor2);	
		    }	     
		     cn.commit();
		     logger.info("HIZO COMMIT");		 
		} catch (Exception e) {
			logger.info("Error al grabar datos ingreso rollbac recaudado" +e); 	   
			if (cn != null) {
	            try {
	                logger.info("Error al grabar datos ingreso rollbac recaudado"); 	               
	                logger.info("NO HIZO COMMIT");
	                logger.info("Error al grabar datos ingreso rollbac"); 
	               cn.rollback();
	               cn.close();
	            } catch(Exception excep) {
	            	logger.info("Error al grabar datos ingreso rollbac" +e); 
	            }
	        }		
		}finally{
			 if (insertCab != null) {
				 insertCab.close();
			 }
			 if (insertDet09 != null) {
				 insertDet09.close();
			 }
			 if (insertNot != null) {
				 insertNot.close();
			 }
			cn.setAutoCommit(true);			
			cn.close();
		}		
	}

	/**
	 * @return the driverClassName
	 */
	public String getDriverClassName() {
		return driverClassName;
	}

	/**
	 * @param driverClassName the driverClassName to set
	 */
	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}

	/**
	 * @return the url
	 */
	public String getUrl() {
		return url;
	}

	/**
	 * @param url the url to set
	 */
	public void setUrl(String url) {
		this.url = url;
	}

	/**
	 * @return the userName
	 */
	public String getUserName() {
		return userName;
	}

	/**
	 * @param userName the userName to set
	 */
	public void setUserName(String userName) {
		this.userName = userName;
	}

	/**
	 * @return the password
	 */
	public String getPassword() {
		return password;
	}

	/**
	 * @param password the password to set
	 */
	public void setPassword(String password) {
		this.password = password;
	}



	/**
	 * @return the driverClassNameODBC
	 */
	public String getDriverClassNameODBC() {
		return driverClassNameODBC;
	}



	/**
	 * @param driverClassNameODBC the driverClassNameODBC to set
	 */
	public void setDriverClassNameODBC(String driverClassNameODBC) {
		this.driverClassNameODBC = driverClassNameODBC;
	}



	/**
	 * @return the urlODBC
	 */
	public String getUrlODBC() {
		return urlODBC;
	}



	/**
	 * @param urlODBC the urlODBC to set
	 */
	public void setUrlODBC(String urlODBC) {
		this.urlODBC = urlODBC;
	}
	
	
	
	
	

}
